﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;

namespace ExcelLibrary
{
    /// <summary>
    /// excel帮助类
    /// <para>index风格从0开始，num风格从1开始</para>
    /// </summary>
    public class ExcelHelper : IDisposable
    {
        private IWorkbook book = null;

        private ISheet currentSheet = null;

        private string fileFullName = null;

        /// <summary>
        /// 当前
        /// </summary>
        public int CurrentSheetIndex
        {
            get
            {
                if (currentSheet == null)
                    return -1;
                return book.GetSheetIndex(currentSheet);
            }
            set
            {
                currentSheet = book.GetSheetAt(value);
                if (currentSheet == null)
                    throw new Exception("没有该工作簿");
            }
        }

        public object this[int rowNum, int cellNum]
        {
            get
            {
                if (currentSheet == null)
                    throw new Exception("未设置当前工作簿");
                IRow row = currentSheet.GetRow(rowNum);
                if (row == null)
                    return null;
                ICell cell = row.GetCell(cellNum);
                if (cell == null)
                    return null;
                return cell.StringCellValue;
            }
            set
            {
                if (currentSheet == null)
                    throw new Exception("未设置当前工作簿");
                IRow row = currentSheet.GetRow(rowNum);
                if (row == null)
                    row = currentSheet.CreateRow(rowNum);
                ICell cell = row.GetCell(cellNum);
                if (cell == null)
                    cell = row.CreateCell(cellNum);
                if (value != null)
                    cell.SetCellValue(value.ToString());
                else
                    cell.SetCellValue("");
            }

        }

        /// <summary>
        /// 带路径的文件全名
        /// </summary>
        /// <param name="fileFullName"></param>
        /// <exception cref="Exception"></exception>
        public ExcelHelper(string fileFullName)
        {
            string fileExt = Path.GetExtension(fileFullName);
            if (string.IsNullOrWhiteSpace(fileExt))
                throw new Exception("文件没有后缀名");
            fileExt = fileExt.ToLower();
            FileStream fs = null;
            try
            {
                fs = File.OpenRead(fileFullName);
                if (fileExt == ".xls")
                    book = new HSSFWorkbook(fs);
                else if (fileExt == ".xlsx")
                    book = new XSSFWorkbook(fs);
                else
                    throw new Exception("非excel文件");
                this.fileFullName = fileFullName;
            }
            catch (Exception ex)
            {
                throw new Exception("打开工作簿异常", ex);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sheetIndex"></param>
        public void SetCurrentSheet(int sheetIndex)
        {
            currentSheet = book.GetSheetAt(sheetIndex);
            if (currentSheet == null)
                throw new Exception("没有该工作簿");
        }

        public void Save()
        {
            Save(this.fileFullName);
        }

        public void Save(string fileFullName)
        {
            if (book == null || string.IsNullOrWhiteSpace(fileFullName))
                return;
            try
            {
                using (FileStream fileStream = File.Open(fileFullName, FileMode.Truncate, FileAccess.Write))
                {
                    book.Write(fileStream);
                    fileStream.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("保存文件异常", ex);
            }
        }

        public void Dispose()
        {
            book = null;
        }
    }
}
